Mega Code Archive

Categories
1) Android
2) ASP.Net
3) ASP.Net Tutorial
4) C
5) C Tutorial
6) C#
7) C# Book
8) C# by API
9) C# Tutorial
10) C++
11) C++ Tutorial
12) Delphi
13) Flash ActionScript
14) Flex
15) Java
16) Java Book
17) Java by API
18) Java Tutorial
19) JavaScript DHTML
20) JavaScript Reference
21) JavaScript Tutorial
22) MSOfficeExcel 2007 Tutorial
23) MSOfficePowerPoint 2007 Tutorial
24) MSOfficeWord 2007 Tutorial
25) MSSQL
26) MSSQL Tutorial
27) MySQL
28) MySQL Tutorial
29) Oracle PLSQL
30) Oracle PLSQL Tutorial
31) Perl
32) Php
33) PostgreSQL
34) Python
35) Python Tutorial
36) Ruby
37) Silverlight
38) VB.Net
39) VB.Net by API
40) VB.Net Tutorial
41) Visual C++ .NET
42) VisualBasic Script
43) XML
44) XML Tutorial

 

MSOfficeExcel 2007 Tutorial
1) Chart
2) Collaboration
3) Data Analysis
4) Database Functions
5) Date Time Functions
6) Editing
7) Engineering Functions
8) Format Style
9) Formula
10) Information Functions
11) Introduction
12) Logical Functions
13) Lookup Reference Functions
14) Macro Activex Add In
15) Math Trigonometry Functions
16) Pivottable Pivotchart
17) Security
18) Statistical Functions
19) Table
20) Text Functions
21) Wordart Clip Art Shape Picture
22) Workbook Worksheet

Lookup Reference Functions
1) =ADDRESS(2,3) returns Absolute reference
2) =ADDRESS(2,3,1,FALSE,[Book1]Sheet1) return absolute reference to another workbook and worksheet
3) =ADDRESS(2,3,1,FALSE,EXCEL SHEET) return absolute reference to another worksheet
4) =ADDRESS(2,3,2) return absolute row; relative column
5) =ADDRESS(2,3,2,FALSE) return absolute row; relative column in R1C1 reference style
6) =AREAS(B2
7) =CHOOSE(2, One, Two, Three)
8) =CHOOSE(4,B2,B3,B4,B5)
9) =COLUMN() returns column in which the formula appears
10) =COLUMN(D3
11) =COLUMN(D3)
12) =COLUMN(Scores) return the first column of the Scores range
13) =COLUMNS({1,2,3;4,5,6}) returns the number of columns in the array constant
14) =FIND(birthday,A1)
15) =FIND(y,A1,10)
16) =HLOOKUP(Name,A1
17) =HLOOKUP(Salary,A1
18) =INDEX((A1
19) =INDEX({1,2;3,4},0,2) returns the value in the first row, second column in the array constant
20) =INDEX(A2
21) =INDEX(B1
22) =INDIRECT($A$3) get the value of the reference in cell A3
23) =INDIRECT(B&$A$5) returns the value of a reference in cell A5
24) =LOOKUP(0,A2
25) =LOOKUP(5,A2
26) =LOOKUP(6 66,A2
27) =LOOKUP(A3,{3,6,7,8,9},{F,D,C,B,A})
28) =LOOKUP(A4,{0,70,73,77,80,83,87,90,93,97},{F,D+,C-,C,C+,B-,B,B+,A-,A,A+})
29) =LOOKUP(abcd,{a,1;b,2;c,3})
30) =MATCH(0,A2
31) =MATCH(3,A2
32) =MATCH(8,A2
33) =OFFSET(A3,0,-2)
34) =OFFSET(A3,-2,0)
35) =OFFSET(C3
36) =OFFSET(C3,2,3,1,1) displays the value in cell F5
37) =ROW() returns the row number in which the formula appears
38) =ROW(C4
39) =ROW(Scores) returns the first row of the Scores range
40) =SUM(B2
41) =SUM(CHOOSE(2,B1
42) =SUM(INDEX(A1
43) =SUM(OFFSET (A3,1,2,50,1))
44) =SUM(OFFSET(C3
45) =VLOOKUP( 7,A2
46) =VLOOKUP(0 1,A2
47) =VLOOKUP(1,A1
48) Always refer to cell A10
49) AREAS returns the number of areas in a reference
50) CHOOSE(index_num,value1,value2, ) chooses a value from a list of values
51) COLUMN(reference) returns the column number of a reference
52) COLUMNS returns the number of columns in an array or reference
53) INDEX Uses an index to choose a value from a reference or array
54) INDIRECT(ref_text,a1) returns a reference indicated by a text value
55) LOOKUP looks up values from a one-row or one-column range or an array
56) LOOKUP(lookup_value,array)
57) MATCH(lookup_value,lookup_array,match_type) returns the relative position of an item in an array
58) OFFSET(reference,rows,cols,height,width) returns a reference offset from a given reference
59) Return a worksheet column letter (ranging from A to XFD) for the value contained in cell A1
60) ROW(reference) returns the row number of a reference
61) ROWS(array) returns the number of rows in a reference
62) To specify several references as a single argument, include extra sets of parentheses
63) TRANSPOSE(array) returns the transpose of an array